-- =============================================
-- Procedure: ChangePassword
-- Purpose:	updates a user password
-- Author: Dale McFarlane
-- Created: 2013-1-20
-- Modification History
-- Modified By	Modification Date	Reason
-- =====================================================================================
--  
-- =====================================================================================

-- EXEC ChangePassword 'kbents', '5f4dcc3b5aa765d61d8327deb882cf99'

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ChangePassword]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.[ChangePassword]
END
GO

CREATE PROCEDURE ChangePassword
@username varchar(50),
@new_password varchar(255)

AS
BEGIN
	DECLARE @error VARCHAR(100)
	DECLARE @error_message VARCHAR(MAX)
	
	BEGIN TRANSACTION
	
	BEGIN TRY	
				
		IF EXISTS(SELECT * FROM dbo.[User] WHERE username = @username)
		BEGIN
			UPDATE dbo.[user]
			SET 
				password = @new_password
			WHERE username = @username
			
		END
		ELSE
		BEGIN
			SET @error = 'ERR0008'
			SET @error_message = 'Password submitted or the User Account is invalid'
		END			
		
	END TRY
	BEGIN CATCH
		SET @error = @@ERROR
		SET @error_message = ERROR_MESSAGE()
	END CATCH
	
	
	SELECT 
		@error AS 'error_code',
		@error_message AS 'error_message'	
	
	IF @error IS NOT NULL 
	BEGIN
		ROLLBACK TRANSACTION;
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION;
	END
END
GO